package com.xbongbong.pro.statistics.helper;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xbongbong.paas.bi.pojo.dto.DataSetParseDTO;
import com.xbongbong.paas.bi.pojo.vo.DataSetResultVO;
import com.xbongbong.paas.constant.PaasConstant;
import com.xbongbong.paas.constant.XbbConstant;
import com.xbongbong.paas.enums.FieldTypeEnum;
import com.xbongbong.paas.enums.FlowStatusEnum;
import com.xbongbong.paas.enums.bi.XbbTableRefEnum;
import com.xbongbong.paas.field.ItemPoJo;
import com.xbongbong.paas.toolbox.exception.XbbException;
import com.xbongbong.paas.toolbox.util.CollectionsUtil;
import com.xbongbong.paas.toolbox.util.DateTimeUtil;
import com.xbongbong.paas.toolbox.util.StringUtil;
import com.xbongbong.pro.bi.commons.helper.StringHelper;
import com.xbongbong.pro.bi.commons.pojo.vo.GroupSqlVO;
import com.xbongbong.pro.dataset.service.ParseSqlService;
import com.xbongbong.pro.domain.entity.ChartEntity;
import com.xbongbong.pro.domain.entity.DataSetEntity;
import com.xbongbong.pro.enums.SaasMarkEnum;
import com.xbongbong.pro.statistic.help.DataSetHelp;
import com.xbongbong.pro.statistic.model.DataSetModel;
import com.xbongbong.pro.statistic.pojo.SlaveSourcePojo;
import com.xbongbong.pro.statistic.result.pojo.FieldAttrPojo;
import com.xbongbong.pro.statistic.service.ChartLimitService;
import com.xbongbong.pro.statistics.constant.DataSetChartConstant;
import com.xbongbong.pro.statistics.help.StatisticsTableVOHelper;
import com.xbongbong.pro.statistics.pojo.dto.GenerateSqlDTO;
import com.xbongbong.pro.statistics.service.StarDataService;
import com.xbongbong.saas.enums.XbbRefTypeEnum;
import com.xbongbong.saas.enums.business.ClueEnum;
import com.xbongbong.saas.enums.business.ContractEnum;
import com.xbongbong.saas.service.toolbox.help.UpdatePackageRedisService;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * 图表中心调用数据集特有方法（其他项目用不到）
 * @ClassName ChartDataSetHelper
 * @Author 梁鲁江
 * 创建时间 2022/9/21 16:06
 * 修改时间  2022/9/21 16:06
 **/
@Component
public class ChartDataSetHelper {
    @Resource
    private ParseSqlService parseSqlSerive;
    @Resource
    private StatisticsTableVOHelper statisticsTableVOHelper;
    @Resource
    private ChartLimitService chartLimitService;
    @Resource
    private StarDataService starDataService;
    @Resource
    private UpdatePackageRedisService updatePackageRedisService;
    @Resource
    private DataSetHelp dataSetHelp;
    @Resource
    private DataSetModel dataSetModel;

    private static final Logger LOG = LoggerFactory.getLogger(ChartDataSetHelper.class);

    /**
     * 获取数据集sql
     * @param generateSqlDTO
     * @param chartEntity
     * @return
     * @throws XbbException
     */
    /*public String getDataSetParseSql(GenerateSqlDTO generateSqlDTO, ChartEntity chartEntity) throws XbbException {
        DataSetParseDTO dataSetParseDTO = new DataSetParseDTO();
        dataSetParseDTO.setCorpid(chartEntity.getCorpid());
        dataSetParseDTO.setLoginUser(generateSqlDTO.getChartResultPojo().getLoginUser());
        dataSetParseDTO.setDataSetId(chartEntity.getDriverSources().getLong("dataSetId"));
        //TODO 后面可能会存下来
        DataSetResultVO dataSetResultVO = parseSqlSerive.parseSql(dataSetParseDTO);
        return dataSetResultVO.getParseSql();
    }*/

    /**
     * 获取数据集sql并将jsonStr传到前端供关联产品渲染用
     * @param generateSqlDTO
     * @param chartEntity
     * @return
     * @throws XbbException
     */
    public DataSetResultVO getDataSetParseSql(GenerateSqlDTO generateSqlDTO, ChartEntity chartEntity) throws XbbException {
        DataSetParseDTO dataSetParseDTO = new DataSetParseDTO();
        dataSetParseDTO.setCorpid(chartEntity.getCorpid());
        dataSetParseDTO.setLoginUser(generateSqlDTO.getChartResultPojo().getLoginUser());
        Long dataSetId = chartEntity.getDriverSources().getLong(XbbConstant.DATA_SET_ID);
        dataSetParseDTO.setDataSetId(dataSetId);

        String corpid = chartEntity.getCorpid();
        //如果数据库中已经存在解析的sql，则直接返回
        DataSetResultVO cacheSqlVo = existParseSql(corpid,dataSetId);
        if(Objects.nonNull(cacheSqlVo)){
            return cacheSqlVo;
        }
        DataSetResultVO dataSetResultVO = parseSqlSerive.parseSql(dataSetParseDTO);
        dataSetResultVO.setJsonStr(dataSetParseDTO.getJsonStr().toJSONString());

        //设置sql到数据集表中
        cacheDataSetSql(corpid,dataSetId,dataSetResultVO.getParseSql());
        return dataSetResultVO;
    }

    /**
     * 设置解析后的sql到数据库中
     *
     * @param corpid
     * @param dataSetId
     * @param parseSql
     * @author zyy
     * @date 2022/12/14
     * @version v1.0
     */
    private void cacheDataSetSql(String corpid, Long dataSetId, String parseSql) throws XbbException {
        DataSetEntity dataSetEntity = dataSetHelp.getDataSetEntity(dataSetId, corpid);
        dataSetEntity.setParseSql(parseSql);
        dataSetEntity.setUpdateTime(DateTimeUtil.getInt());
        dataSetModel.update(dataSetEntity);
    }

    /**
     * 数据集中是否已经存在解析的sql
     *
     * @param corpid 公司唯一标识
     * @param dataSetId 数据集主键id
     * @return com.xbongbong.paas.bi.pojo.vo.DataSetResultVO
     * @author zyy
     * @date 2022/12/14
     * @version v1.0
     */
    private DataSetResultVO existParseSql(String corpid,Long dataSetId) throws XbbException {
        DataSetEntity dataSetEntity = dataSetHelp.getDataSetEntity(dataSetId, corpid);
        String parseSql = dataSetEntity.getParseSql();
        if(StringUtils.isBlank(parseSql)){
            return null;
        }
        DataSetResultVO cacheSqlVo = new DataSetResultVO();
        cacheSqlVo.setParseSql(parseSql);
        cacheSqlVo.setJsonStr(dataSetEntity.getBoard().toJSONString());
        return cacheSqlVo;
    }

    /**
     * 通用sql
     * @param colums   显示字段
     * @param parseSql 数据集语句
     * @param filter  查询筛选语句
     * @return
     */
    public StringBuffer getCommonSql(String colums, String parseSql, String filter) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(XbbConstant.SELECT);
        stringBuffer.append(colums);
        stringBuffer.append(XbbConstant.FROM);
        stringBuffer.append(XbbConstant.L_P);
        stringBuffer.append(parseSql);
        stringBuffer.append(XbbConstant.R_P).append(XbbConstant.SPACE);
        stringBuffer.append(DataSetChartConstant.DATA_RESULT);
        //筛选
        if(StringUtil.isNotEmpty(filter)){
            stringBuffer.append(XbbConstant.SPACE).append(filter).append(XbbConstant.SPACE);
        }
        return stringBuffer;
    }


    /**
     * 图表中心sql语句拼装
     * @param parseSql  数据集语句
     * @param columFields   显示字段
     * @param sortField  排序字段
     * @param filter  查询筛选字段
     * @param pageSize  分页大小
     * @param from 起始位置
     * @param groupFields  分组字段
     * @param targetFlag  是否是指标图
     * @throws XbbException
     */
    public StringBuffer tableSqlAppend(String parseSql, String columFields,
                                       String sortField, String filter, Integer pageSize, Integer from, String groupFields, boolean targetFlag, GroupSqlVO groupSqlVO) throws XbbException {
        //查询字段替换
        columFields = replaceTeamAttrAlisa(columFields);
        //分组字段替换
        groupFields = replaceTeamAttrAlisa(groupFields);
        //子查询外层字段替换
        boolean subOfCount = Objects.nonNull(groupSqlVO) && CollectionsUtil.isNotEmpty(groupSqlVO.getSubQuerySqlOfCountList());
        if(subOfCount){
            String replaceTeamAttrAlisa = replaceTeamAttrAlisa(groupSqlVO.getSubSqlOfCount());
            groupSqlVO.setSubSqlOfCount(replaceTeamAttrAlisa);
        }

        //恒查id_1
        if (StringUtils.isNotEmpty(columFields) && !columFields.contains(XbbConstant.ID_ONE)){
            if (StringUtils.contains(parseSql,XbbConstant.ID_ONE)){
                columFields = columFields + XbbConstant.SQL_COMMA +XbbConstant.ID_ONE;
                if(subOfCount){
                    String subSqlOfCount = groupSqlVO.getSubSqlOfCount();
                    groupSqlVO.setSubSqlOfCount(subSqlOfCount + XbbConstant.SQL_COMMA +XbbConstant.ID_ONE);
                }
            }
        }
        StringBuffer stringBuffer = getCommonSql(columFields, parseSql, filter);
        if (StringUtil.isNotEmpty(groupFields)) {
            stringBuffer.append(XbbConstant.GROUP_BY).append(groupFields);
        }
        //排序
        dsFieldSort(stringBuffer,sortField, groupSqlVO);

        //limit from ,pageSize 
        if (from != null && pageSize!=null && pageSize>0){
            stringBuffer.append(XbbConstant.LIMIT).append(from).append(XbbConstant.SQL_COMMA).append(pageSize);
        }

        if(Objects.nonNull(groupSqlVO)){
            StringBuffer subSqlBuilder = new StringBuffer();
            List<String> subQuerySqlOfCountList = groupSqlVO.getSubQuerySqlOfCountList();
            if(CollectionsUtil.isNotEmpty(subQuerySqlOfCountList)){
                //存储子查询内层完整SQL语句;
                groupSqlVO.setSubCompleteSqlOfCount(stringBuffer.toString());

                //子查询外层语句拼接(计算公式中存在COUNT函数的计算字段)
                String subSqlItem = groupSqlVO.getSubSqlOfCount();
                String subOrderSqlOfCount = groupSqlVO.getSubOrderSqlOfCount();

                subSqlBuilder.append(XbbConstant.SELECT).append(subSqlItem).append(XbbConstant.SPACE)
                        .append(XbbConstant.FROM).append(XbbConstant.L_P).append(stringBuffer).append(XbbConstant.R_P).append(XbbConstant.SPACE);
                subSqlBuilder.append(XbbConstant.AS).append("T");
                if(StringUtils.isNotBlank(subOrderSqlOfCount)){
                    subSqlBuilder.append(XbbConstant.ORDER_BY).append(subOrderSqlOfCount);
                }
                //重置sql
                stringBuffer.delete(0,stringBuffer.length());
                stringBuffer.append(subSqlBuilder);
            }
        }
        return stringBuffer;
    }

    private String replaceTeamAttrAlisa(String fields) {
        if (StringUtils.isNotEmpty(fields) &&
                (fields.contains(FieldTypeEnum.OWNERID.getAlias()) ||
                        fields.contains(FieldTypeEnum.COUSERID.getAlias()))){
            String[] columns = fields.split(XbbConstant.SQL_COMMA);
            return Arrays.stream(columns).map(column -> {
                if (column.contains(FieldTypeEnum.OWNERID.getAlias())) {
                    column= StringHelper.replaceTeamAttr(column, XbbConstant.MY_TEAM_MAIN);
                } else if (column.contains(FieldTypeEnum.COUSERID.getAlias())) {
                    column= StringHelper.replaceTeamAttr(column, XbbConstant.MY_TEAM);
                }
                return column;
            }).collect(Collectors.joining(XbbConstant.SQL_COMMA));
        }
        return fields;
    }

    private void dsFieldSort(StringBuffer stringBuffer,String sortField, GroupSqlVO groupSqlVO) {
        //手动排序后就不再单独根据字段调整
        if(StringUtils.isNotBlank(sortField)){
            stringBuffer.append(XbbConstant.ORDER_BY).append(sortField);
        }else if (Objects.nonNull(groupSqlVO)) {
            dsComboFieldSortOrDefault(stringBuffer,groupSqlVO);
        }
    }

    private void dsComboFieldSortOrDefault(StringBuffer stringBuffer, GroupSqlVO groupSqlVO) {
        Map<String, FieldAttrPojo> comboFieldMap = groupSqlVO.getComboFieldMap();
        List<String> groupFieldsList = groupSqlVO.getGroupFieldsList();
        if(CollectionsUtil.isNotEmpty(groupFieldsList)){
            //starRocks不支持数组类型的排序，删除掉
            groupFieldsList.removeIf(str -> str.contains(XbbConstant.MY_TEAM_MAIN) || str.contains(XbbConstant.MY_TEAM) || str.contains(FieldTypeEnum.OWNERID.getAlias())  || str.contains(FieldTypeEnum.COUSERID.getAlias()));
        }
        List<String> tmpSqlList = new ArrayList<>();
        //没有下拉单选的字段
        if(comboFieldMap == null || comboFieldMap.size() == 0){
            //使用原始sql
            tmpSqlList = groupFieldsList;
        }else{
            //置换下拉字段的sql
            for (String groupSql : groupFieldsList) {
                if (comboFieldMap.containsKey(groupSql)) {
                    FieldAttrPojo fieldAttrPojo = comboFieldMap.get(groupSql);
                    String caseWhen = caseWhen(groupSql,fieldAttrPojo.getItems(),"");
                    if (StringUtils.isNotBlank(caseWhen)) {
                        tmpSqlList.add(caseWhen);
                    }
                } else {
                    tmpSqlList.add(groupSql);
                }
            }
        }
        statisticsTableVOHelper.defaultFieldSort(stringBuffer,tmpSqlList);
    }

    public String caseWhen(String key, List<ItemPoJo> whenItems, String ...thenStr) {
        if(CollectionsUtil.isEmpty(whenItems)){
            return "";
        }
        StringBuilder sb = new StringBuilder();
        sb.append(XbbConstant.CASE).append(XbbConstant.SPACE);
        //主条件
        for (int i = 0; i < whenItems.size(); i++) {
            sb.append(XbbConstant.WHEN).append(XbbConstant.SPACE).append(key).append(XbbConstant.EQUAL)
                    .append(XbbConstant.SINGLE_QUOTES).append(whenItems.get(i).getValue()).append(XbbConstant.SINGLE_QUOTES)
                    .append(XbbConstant.SPACE)
                    .append(XbbConstant.THEN).append(XbbConstant.SPACE).append(whenItems.size() - i).append(XbbConstant.SPACE);
        }
        sb.append(XbbConstant.END);
        return sb.toString();
    }

    /**
     * 校验表单数据量是否超过限制
     *
     * @param corpid 公司唯一标识
     * @param driverSources 主表数据源
     * @param slaveSources 从表数据源
     * @param single 0-多表 1-单表 2-数据集
     * @author zyy
     * @date 2022/11/2
     * @version v1.0
     */
    public void chartDataLimit(String corpid, JSONObject driverSources, JSONArray slaveSources, Integer single) throws XbbException {
        //获取数据源数组
        List<SlaveSourcePojo> selectList = chartLimitService.getSelectList(corpid, single, driverSources);
        //获取图表数据量限制
        Integer limitNum = chartLimitService.getChartDataLimitNum(corpid, selectList, single, slaveSources);
        dataLimit(selectList, corpid, limitNum);
    }

    /**
     * 校验数据量
     *
     * @param selectList 表单实体数组
     * @param corpid 公司唯一标识
     * @param biDataLimit 限制数据量
     * @author zyy
     * @date 2022/11/2
     * @version v1.0
     */
    public void dataLimit(List<SlaveSourcePojo> selectList, String corpid, Integer biDataLimit) throws XbbException {
        //获取公司下表单已用数据量的缓存
        String jsonStr = updatePackageRedisService.getChartDataLimitCache(corpid);
        if(StringUtil.isEmpty(jsonStr)){
            //从来没用过该公司的表单,则从es库中查询表单使用的数据量
            Map<String, Long> countMap = new HashMap<>();
            chartCountMap4Ds(corpid, selectList, countMap);
            //更新公司下表单已用数据量的缓存
            updatePackageRedisService.refreshChartDataLimitCache(corpid,countMap);
            //校验是否图表已用数据量是否超过限制
            updatePackageRedisService.checkDataCount(corpid,selectList,countMap,biDataLimit);
        }else {
            //用过该公司下的表单，但是不排除部分表单没选过
            JSONObject allSource = JSON.parseObject(jsonStr);
            //校验已使用表单的数据量，并组装没有使用过的表单
            List<SlaveSourcePojo> noUseList = updatePackageRedisService.getNoUseList(corpid,selectList,allSource,biDataLimit);
            //校验没有使用过的表单数据量
            if(CollectionsUtil.isNotEmpty(noUseList)){
                Map<String, Long> countMap = new HashMap<>(PaasConstant.DEFAULT_INITIAL_CAPACITY);
                chartCountMap4Ds(corpid, noUseList, countMap);
                //把原来未用表单的数据量追加到缓存里
                allSource.putAll(countMap);
                //更新公司下表单已用数据量的缓存
                updatePackageRedisService.refreshChartDataLimitCache(corpid,countMap);
                updatePackageRedisService.checkDataCount(corpid,noUseList,countMap,biDataLimit);
            }
        }
    }

    public void chartCountMap4Ds(String corpid, List<SlaveSourcePojo> selectList, Map<String, Long> countMap) {
        for (SlaveSourcePojo slaveSourcePojo : selectList) {
            XbbRefTypeEnum xbbRefTypeEnum = null;
            if (Objects.equals(SaasMarkEnum.SAAS.getCode(), slaveSourcePojo.getSaasMark())) {
                xbbRefTypeEnum = XbbRefTypeEnum.getByCode(XbbRefTypeEnum.transferBusinessType4Distributor(slaveSourcePojo.getBusinessType(),slaveSourcePojo.getDistributorMark()));
            } else {
                xbbRefTypeEnum = XbbRefTypeEnum.PAAS;
            }
            getChartCountMap(countMap, corpid, slaveSourcePojo.getFormId(), xbbRefTypeEnum);
        }
    }

    private void getChartCountMap(Map<String, Long> countMap, String corpid, Long formId, XbbRefTypeEnum xbbRefTypeEnum) {
        String tableName = "";
        List<String> filterList = defaultFilter(corpid,formId);
        XbbTableRefEnum xbbTableRefEnum;
        switch (xbbRefTypeEnum) {
            case CUSTOMER_COMMUNICATE:
                xbbTableRefEnum = XbbTableRefEnum.getByCode(XbbRefTypeEnum.CUSTOMER_COMMUNICATE.getCode());
                tableName = xbbTableRefEnum.getTableName();
                filterList.add("log_type" + XbbConstant.EQUAL + XbbConstant.ZERO);
                break;
            case  DISTRIBUTOR_MANAGEMENT:
                xbbTableRefEnum = XbbTableRefEnum.getByCode(XbbRefTypeEnum.CUSTOMER_MANAGEMENT.getCode());
                tableName = xbbTableRefEnum.getTableName();
                break;
            case DISTRIBUTOR_CONTACT:
                xbbTableRefEnum = XbbTableRefEnum.getByCode(XbbRefTypeEnum.CONTRACT.getCode());
                tableName = xbbTableRefEnum.getTableName();
                break;
            case DISTRIBUTOR_COMMUNICATE:
                xbbTableRefEnum = XbbTableRefEnum.getByCode(XbbRefTypeEnum.CUSTOMER_COMMUNICATE.getCode());
                tableName = xbbTableRefEnum.getTableName();
                break;
            case ORDER:
                xbbTableRefEnum = XbbTableRefEnum.getByCode(XbbRefTypeEnum.CONTRACT.getCode());
                tableName = xbbTableRefEnum.getTableName();
                filterList.add(ContractEnum.IS_RED.getAttr() + XbbConstant.EQUAL + XbbConstant.ZERO);
                filterList.add(ContractEnum.ARCHIVED.getAttr() + XbbConstant.EQUAL + XbbConstant.TWO);
                break;
            case RETURN:
                xbbTableRefEnum = XbbTableRefEnum.getByCode(XbbRefTypeEnum.REFUND.getCode());
                tableName = xbbTableRefEnum.getTableName();
                break;
            case ORDER_OUTSTOCK:
                xbbTableRefEnum = XbbTableRefEnum.getByCode(XbbRefTypeEnum.OUTSTOCK.getCode());
                tableName = xbbTableRefEnum.getTableName();
                break;
            case CLUE:
                xbbTableRefEnum = XbbTableRefEnum.getByCode(XbbRefTypeEnum.CLUE.getCode());
                tableName = xbbTableRefEnum.getTableName();
                filterList.add(ClueEnum.IS_PUBLIC.getAttr() + XbbConstant.EQUAL + XbbConstant.ZERO);
                filterList.add(ClueEnum.IS_ARCHIVED.getAttr() + XbbConstant.EQUAL + XbbConstant.TWO);
                break;
            case CLUE_PUBLIC:
            case CLUE_RECYCLE:
                xbbTableRefEnum = XbbTableRefEnum.getByCode(XbbRefTypeEnum.CLUE.getCode());
                tableName = xbbTableRefEnum.getTableName();
                break;
            case PAAS:
                xbbTableRefEnum = XbbTableRefEnum.getByCode(XbbConstant.ZERO);
                tableName = xbbTableRefEnum.getTableName();
                break;
            default:
                xbbTableRefEnum = XbbTableRefEnum.getByCode(xbbRefTypeEnum.getCode());
                tableName = xbbTableRefEnum.getTableName();
                break;
        }
        //过滤无form_id字段的表单
        if(XbbRefTypeEnum.supportFormId(xbbTableRefEnum)){
            filterList.add("form_id" + XbbConstant.EQUAL + formId);
        }
        //过滤无flowStatus字段的表单
        if(XbbRefTypeEnum.supportFlowStatus(xbbTableRefEnum)){
            filterList.add("flow_status in " + XbbConstant.L_P + FlowStatusEnum.NOT_NEED_APPROVAL.getType() + XbbConstant.SQL_COMMA + FlowStatusEnum.PASS.getType() + XbbConstant.R_P);
        }

        StringBuilder countSql = new StringBuilder();
        countSql.append(XbbConstant.SELECT);
        countSql.append(XbbConstant.COUNT_ALL);
        countSql.append(XbbConstant.FROM);
        countSql.append(tableName);
        countSql.append(XbbConstant.SPACE);
        countSql.append(XbbConstant.WHERE).append(XbbConstant.TRUE).append(XbbConstant.AND);
        countSql.append(String.join(XbbConstant.AND,filterList));

        long count;
        try {
            Integer num = starDataService.countBySql(countSql.toString());
            count = num.longValue();
        }catch (Exception e){
            count = 0L;
            LOG.error("数据集图表的表单数据量查询失败：",e);
        }
        countMap.put(formId.toString(),count);
    }

    private List<String> defaultFilter(String corpid, Long formId) {
        List<String> filterList = new ArrayList<>();
        filterList.add("corpid" + XbbConstant.EQUAL + XbbConstant.SINGLE_QUOTES + corpid + XbbConstant.SINGLE_QUOTES);
        filterList.add("del" + XbbConstant.EQUAL + XbbConstant.ZERO);
        return filterList;
    }

}
